import pymysql
params = {
    "host": "localhost",
    "user": "root",
    "port": 3306,
    "password": "yxtdemysql",
    "db": "text_1"
}
con_local = pymysql.connect(**params)
local_cursor = con_local.cursor()

def one():
    sql = "select store_name, sum(amount) from my_oder group by store_name;"
    local_cursor.execute(sql)
    con_local.commit()

def two():
    sql = """
    select t2.department, t1.store_name, sum(o) from
(select store_name, sum(amount) as o from my_oder group by store_name)t1
join
(select store_name, department from depart_store)t2
on t1.store_name = t2.store_name 
group by t2.department;
    """
    print(sql)
    local_cursor.execute(sql)
    con_local.commit()

def three():
    sql = """select sum(case when t.j>16000 then 1 else 0 end)/count(*) as 达标率 from (
select t2.department, t1.store_name, sum(o)as j from
(select store_name, sum(amount) as o from my_oder group by store_name)t1
join
(select store_name, department from depart_store)t2
on t1.store_name = t2.store_name 
group by t2.department
)t"""
    local_cursor.execute(sql)
    con_local.commit()

def four():
    sql = """select department,
count(*),
sum(amount)as total,
sum(case when amount>700 then 1 else 0 end)/count(*)
from
(
(select * from my_oder)as t1
join
(select * from depart_store)as t2
on t1.store_name = t2.store_name
)
group by department"""
    # 统计各个店铺下单个订单金额超过600的总数和比率
    sql_1 = """select t1.store_name,
sum(case when amount > 600 then 1 else 0 end)as number,
sum(case when amount > 600 then 1 else 0 end)/count(*)
from
(
(select * from my_oder)as t1
join
(select * from depart_store)as t2
on t1.store_name = t2.store_name
)
group by t1.store_name"""
    local_cursor.execute(sql)
    con_local.commit()

def five():
    sql_1 = """select t.department
,t.oi
from
(
select t2.department
,sum(amount)as oi from
(select * from my_oder)t1
join
(select * from depart_store)t2
on t1.store_name = t2.store_name
group by t2.department
) t
where t.oi>16000"""
    sql_2 ="""select department,
sum(amount)as d from
my_oder as w
join
depart_store as e
on w.store_name = e.store_name
group by department
having d>16000
    """
    local_cursor.execute(sql_1)
    con_local.commit()

if __name__ == "__main__":
    # one()
    two()
    # three()
    # four()
    # five()